﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Fotoalbum.DAL
{
    public class DataAdapter : IDataAdapter
    {

        string _connectionString = "Data Source=.\\SQLEXPRESS;"
                                 + "AttachDbFilename=\"C:\\Users\\jan\\Documents\\Visual Studio 2008\\Projects\\Fotoalbum\\Fotoalbum.DAL\\FotoSammlung.mdf\";"
                                 + "Integrated Security=True;"
                                 + "Connect Timeout=30;"
                                 + "User Instance=True";

        SqlCommand SqlCommandErstellen(string statment)
        {
            return new SqlCommand(statment, new SqlConnection(_connectionString));
        }

        #region Singelton

        private static DataAdapter _instance;
        private DataAdapter() 
        {
        }

        public static DataAdapter CreateInstance()
        {
            if (_instance == null)
            {
                _instance = new DataAdapter();
            }

            return _instance;
        }

        #endregion Singelton

        #region private Datenbank Zugriffe

        private List<Fotoalbum.Core.BildData> BilderAuslesenByAlbumID(Guid albumID)
        {
            SqlConnection con = new SqlConnection(_connectionString);
            string statment = SqlStatments.BilderAuslesenByA_ID;
            SqlCommand cmd = new SqlCommand(statment, con);
            cmd.Parameters.AddWithValue("@A_ID", albumID);

            List<Core.BildData> bilder = new List<Core.BildData>();

            con.Open();
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    bilder.Add(TransformSqlDataToBildData(reader));
                }
            }
            finally
            {
                if (con != null)
                    if (con.State == ConnectionState.Open)
                        con.Close();
            }

            return bilder;
        }
        private List<Core.BildMarkierungData> MarkierungenAuslesen(Guid bildID)
        {
            List<Core.BildMarkierungData> markierungen = new List<Core.BildMarkierungData>();

            SqlConnection con = new SqlConnection(_connectionString);
            string statment = SqlStatments.MarkierungenAuslesenByB_ID;
            SqlCommand cmd = new SqlCommand(statment, con);
            cmd.Parameters.AddWithValue("@B_ID", bildID);

            con.Open();
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                     markierungen.Add(TransformSqlDataToMarkierungData(reader));
                }

            }
            finally
            {
                if (con != null)
                    if (con.State == ConnectionState.Open)
                        con.Close();
            }

            return markierungen;
        }

        #endregion private Datenbank Zugriffe

        #region Transform - Methoden

        private Core.BildMarkierungData TransformSqlDataToMarkierungData(SqlDataReader reader)
        {
            Guid id = (Guid)reader["M_ID"];
            Guid bildID = (Guid)reader["M_ID_B_ID"];
            Guid stammbuchEintragID = (Guid)reader["M_ID_SE_ID"];
            int positionX = (int)reader["n_M_ID_PositionX"];
            int positionY = (int)reader["n_M_ID_PositionY"];
            int breite = (int)reader["n_M_ID_Breite"];
            int höhe = (int)reader["n_M_ID_Höhe"];
            return new Core.BildMarkierungData(id,
                                               bildID,
                                               stammbuchEintragID,
                                               positionX,
                                               positionY,
                                               breite,
                                               höhe);
        }

        private Core.AlbumData TransformSqlDataToAlbumData(SqlDataReader reader)
        {
            Guid id = (Guid)reader["A_ID"];
            string name = (string)reader["n_A_ID_Bezeichnung"];
            List<Core.BildData> bilder = BilderAuslesenByAlbumID(id);
            return new Core.AlbumData(id, name, bilder);
        }

        private Core.BildData TransformSqlDataToBildData(SqlDataReader reader)
        {
            Guid id = (Guid)reader["B_ID"];
            string dateipfad = (string)reader["n_B_ID_Dateipfad"];
            List<Core.BildMarkierungData> markierungen = MarkierungenAuslesen(id);
            return new Core.BildData(id, dateipfad, markierungen);
        }

        #endregion Transform - Methoden

        #region IDataAdapter Members

        public bool BildHinzufügen(Fotoalbum.Core.BildData bildData)
        {
            SqlCommand cmd = SqlCommandErstellen(SqlStatments.BildHinzufügen);
            cmd.Parameters.AddWithValue("@B_ID", bildData.BildID);
            cmd.Parameters.AddWithValue("@n_B_ID_Dateipfad", bildData.DateiPfad);

            cmd.Connection.Open();
            try
            {
                if (cmd.ExecuteScalar() != null)
                    return true;
            }
            finally
            {
                if (cmd.Connection != null)
                    if (cmd.Connection.State == ConnectionState.Open)
                        cmd.Connection.Close();
            }

            return false;
        }

        public Fotoalbum.Core.BildData BildAuslesen(Guid bildID)
        {
            SqlCommand cmd = SqlCommandErstellen(SqlStatments.BildAuslesenByB_ID);
            cmd.Parameters.AddWithValue("@B_ID", bildID);

            cmd.Connection.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            try
            {
                if (reader.Read())
                {
                    return TransformSqlDataToBildData(reader);
                }
            }
            finally
            {
                if (cmd.Connection != null)
                    if (cmd.Connection.State == ConnectionState.Open)
                        cmd.Connection.Close();
            }

            return null;
        }

        public Fotoalbum.Core.BildData BildAuslesen(string pfad)
        {
            SqlCommand cmd = SqlCommandErstellen(SqlStatments.BildAuslesenByn_B_ID_Dateipfad);
            cmd.Parameters.AddWithValue("@n_B_ID_Dateipfad", pfad);

            cmd.Connection.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            try
            {
                if (reader.Read())
                {
                    return TransformSqlDataToBildData(reader);
                }
            }
            finally
            {
                if (cmd.Connection != null)
                    if (cmd.Connection.State == ConnectionState.Open)
                        cmd.Connection.Close();
            }

            return null;
        }

        public List<Fotoalbum.Core.BildData> AlleBilderAuslesen()
        {
            return BilderAuslesen("");
        }

        public List<Fotoalbum.Core.AlbumData> AlleAlbenAuslesen()
        {
            SqlCommand cmd = SqlCommandErstellen(SqlStatments.AlbenAuslesen);

            List<Core.AlbumData> alben = new List<Core.AlbumData>();

            cmd.Connection.Open();
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    alben.Add(TransformSqlDataToAlbumData(reader));
                }
            }
            finally
            {
                if (cmd.Connection != null)
                    if (cmd.Connection.State == ConnectionState.Open)
                        cmd.Connection.Close();
            }

            return alben;
        }

        public List<Core.BildData> BilderAuslesen(string whereClause)
        {
            SqlCommand cmd = SqlCommandErstellen(SqlStatments.BilderAuslesen + whereClause);

            List<Core.BildData> bilder = new List<Core.BildData>();

            cmd.Connection.Open();
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    bilder.Add(TransformSqlDataToBildData(reader));
                }
            }
            finally
            {
                if (cmd.Connection != null)
                    if (cmd.Connection.State == ConnectionState.Open)
                        cmd.Connection.Close();
            }

            return bilder;
        }

        #endregion
    }
}
